﻿using System;
using System.Collections.Generic;
using System.Data.SQLite; // 引入SQLite命名空间

namespace CursorCreat
{
    internal class Program
    {
        static void Main(string[] args)
        {
        }
    }

    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string StudentNumber { get; set; }
        public string Major { get; set; }
    }

    // 学生管理系统类
    public class StudentManagementSystem
    {
        private SQLiteConnection sqliteConn;

        // 构造函数，初始化数据库连接
        public StudentManagementSystem(string dbPath)
        {
            sqliteConn = new SQLiteConnection($"Data Source={dbPath};Version=3;");
            sqliteConn.Open();
            InitializeDatabase();
        }

        // 初始化数据库，创建学生表
        private void InitializeDatabase()
        {
            string sql = @"CREATE TABLE IF NOT EXISTS Students (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        Name TEXT NOT NULL,
                        StudentNumber TEXT NOT NULL,
                        Major TEXT NOT NULL)";
            SQLiteCommand command = new SQLiteCommand(sql, sqliteConn);
            command.ExecuteNonQuery();
        }

        // 添加学生信息
        public void AddStudent(Student student)
        {
            string sql = "INSERT INTO Students (Name, StudentNumber, Major) VALUES (@Name, @StudentNumber, @Major)";
            SQLiteCommand command = new SQLiteCommand(sql, sqliteConn);
            command.Parameters.AddWithValue("@Name", student.Name);
            command.Parameters.AddWithValue("@StudentNumber", student.StudentNumber);
            command.Parameters.AddWithValue("@Major", student.Major);
            command.ExecuteNonQuery();
        }

        // 查询学生信息
        public List<Student> QueryStudents(string keyword)
        {
            string sql = "SELECT * FROM Students WHERE Name LIKE @Keyword OR StudentNumber LIKE @Keyword";
            SQLiteCommand command = new SQLiteCommand(sql, sqliteConn);
            command.Parameters.AddWithValue("@Keyword", $"%{keyword}%");
            SQLiteDataReader reader = command.ExecuteReader();
            List<Student> studeCurnts = new List<Student>();
            while (reader.Read())
            {
                studeCurnts.Add(new Student
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    Name = reader["Name"].ToString(),
                    StudentNumber = reader["StudentNumber"].ToString(),
                    Major = reader["Major"].ToString()
                });
            }
            return studeCurnts;
        }

        // 修改学生信息
        public void UpdateStudent(Student student)
        {
            string sql = "UPDATE Students SET Name = @Name, StudentNumber = @StudentNumber, Major = @Major WHERE Id = @Id";
            SQLiteCommand command = new SQLiteCommand(sql, sqliteConn);
            command.Parameters.AddWithValue("@Id", student.Id);
            command.Parameters.AddWithValue("@Name", student.Name);
            command.Parameters.AddWithValue("@StudentNumber", student.StudentNumber);
            command.Parameters.AddWithValue("@Major", student.Major);
            command.ExecuteNonQuery();
        }

        // 删除学生信息
        public void DeleteStudent(int id)
        {
            string sql = "DELETE FROM Students WHERE Id = @Id";
            SQLiteCommand command = new SQLiteCommand(sql, sqliteConn);
            command.Parameters.AddWithValue("@Id", id);
            command.ExecuteNonQuery();
        }

        // 展示所有学生信息
        public List<Student> GetAllStudents()
        {
            string sql = "SELECT * FROM Students";
            SQLiteCommand command = new SQLiteCommand(sql, sqliteConn);
            SQLiteDataReader reader = command.ExecuteReader();
            List<Student> students = new List<Student>();
            while (reader.Read())
            {
                students.Add(new Student
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    Name = reader["Name"].ToString(),
                    StudentNumber = reader["StudentNumber"].ToString(),
                    Major = reader["Major"].ToString()
                });
            }
            return students;
        }

        // 析构函数，关闭数据库连接
        ~StudentManagementSystem()
        {
            sqliteConn.Close();
        }
    }
}
